﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using DBUnitility;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    public class TrafficInfoService
    {

        #region 获取所有交通管制的配置
        /// <summary>
        /// 获取所有交通管制的配置
        /// </summary>
        /// <returns></returns>
        public List<TrafficInfo> GetAlltrafficInfo()
        {
            string sql = "select ID,LandNum,CMD,CMDName,Area from TrafficInfo";
            List<TrafficInfo> list = new List<TrafficInfo>();
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                while (objReader.Read())
                {
                    list.Add(new TrafficInfo()
                   {
                       ID = Convert.ToInt32(objReader["ID"]),
                       LandNum = Convert.ToInt32(objReader["LandNum"]),
                       CMD = Convert.ToInt32(objReader["CMD"]),
                       CMDName = objReader["CMDName"].ToString(),
                       Area = Convert.ToInt32(objReader["Area"])
                   });
                }
                objReader.Close();
                return list;
            }
            catch (Exception ex)
            {               
                throw new Exception("交通管制信息获取失败，错误详细信息："+ ex.Message);
            }
        }
        #endregion

        #region 用过地标号获取所有对应的交管功能
        /// <summary>
        /// 用过地标号获取所有对应的交管功能
        /// </summary>
        /// <param name="landNum">地标号</param>
        /// <returns>交通功能集合</returns>
        public List<TrafficInfo> GettrafficInfoByLandNum(string landNum)
        {
            string sql = "select LandNum,CMD,CMDName,Area from TrafficInfo where LandNum = {0}";
            sql = string.Format(sql, landNum);
            List<TrafficInfo> list = new List<TrafficInfo>();
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                while (objReader.Read())
                {
                    list.Add(new TrafficInfo()
                    {
                      //  ID = Convert.ToInt32(objReader["ID"]),
                        LandNum = Convert.ToInt32(objReader["LandNum"]),
                        CMD = Convert.ToInt32(objReader["CMD"]),
                        CMDName = objReader["CMDName"].ToString(),
                        Area = Convert.ToInt32(objReader["Area"])
                    });
                }
                objReader.Close();
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("交通管制信息获取失败，错误详细信息：" + ex.Message);
            }
        }
        #endregion


        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="objTraffic"></param>
        /// <returns></returns>
        public int AddTrafficInfo(TrafficInfo objTraffic)
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@LandNum",objTraffic.LandNum),
                new SqlParameter("@CMD",objTraffic.CMD),
                new SqlParameter("@CMDName",objTraffic.CMDName),
                new SqlParameter("@Area",objTraffic.Area),
            };
            return Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_AddTraffic", param));
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="objTraffic"></param>
        /// <returns></returns>
        public int ModifyTrafficInfo(TrafficInfo objTraffic)
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                 new SqlParameter("@ID",objTraffic.ID),
                new SqlParameter("@LandNum",objTraffic.LandNum),
                new SqlParameter("@CMD",objTraffic.CMD),
                new SqlParameter("@CMDName",objTraffic.CMDName),
                new SqlParameter("@Area",objTraffic.Area),
            };
            return Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_ModifyTraffic", param));
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteTrafficInfo(string id)
        {
            return Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_DeleteTraffic", new SqlParameter[] { new SqlParameter("@ID", id) }));
        }
        /// <summary>
        /// 避免相同指令重复输入
        /// </summary>
        /// <param name="landNum"></param>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public int IsExitsTraffic(string landNum, string cmd,string area)
        {
            string sql = "select count(*) from TrafficInfo where LandNum ={0} and CMD ={1} and Area ={2}";
            sql = string.Format(sql,landNum,cmd,area);
            return Convert.ToInt32(SQLHelper.GetSingalResult(sql));
        }
        /// <summary>
        /// 按条件组合查询交管信息
        /// </summary>
        /// <param name="landNum">地标号</param>
        /// <param name="cmdName">命令名称</param>
        /// <param name="area">区域号</param>
        /// <returns></returns>
        public List<TrafficInfo> QueryTrafficInfo(string landNum, string cmdName, string area)
        {
            string sql = "select ID,LandNum,CMD,CMDName,Area from TrafficInfo where 1=1";
            if (landNum.Trim().Length != 0)
            {
                sql += string.Format(" and LandNum={0}", landNum);
            }
            if (cmdName.Trim().Length != 0)
            {
                sql += string.Format(" and CMDName='{0}'", cmdName);
            }
            if (area.Trim().Length != 0)
            {
                sql += string.Format(" and Area={0}", area);
            }
            List<TrafficInfo> list = new List<TrafficInfo>();
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                while (objReader.Read())
                {
                    list.Add(new TrafficInfo() 
                    { 
                        ID =Convert.ToInt32( objReader["ID"]),
                        LandNum =Convert.ToInt32(objReader["LandNum"]),
                        CMD = Convert.ToInt32(objReader["CMD"]),
                        CMDName = objReader["CMDName"].ToString(),
                        Area = Convert.ToInt32(objReader["Area"])
                    
                    });
                }
                objReader.Close();
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("交管信息查询失败!详细信息："+ex.Message);
            }
            
        }
    }
}
